
import logging
import sqlite3

from mycrawl.spiderutils.log_helper import get_mylogger

# from eolcrawl.settings import LOG_PATH


# logging.basicConfig(level=logging.INFO,
#                     format='%(asctime)s %(filename)s %(levelname)s %(message)s',
#                     datefmt='%a %d %b %Y %H:%M:%S',
#                     filename=LOG_PATH+"/sqlitecom.log",
#                     )
logging = get_mylogger("sqlitecom",level=logging.INFO)

class SqliteUtil:
    dbname = None
    tblname = None
    con = None
    cur = None

    def __init__(self,dbname,tblname):
        self.dbname = dbname
        self.tblname = tblname
        try:
            ## Create/Connect to database
            self.con = sqlite3.connect(dbname)

            ## Create cursor, used to execute commands
            self.cur = self.con.cursor()

            ## Create table if none exists
            self.cur.execute(tblname)
            logging.info("sqlite create db success")
        except Exception as e:
            logging.exception(f"sqlite create db failed,{e}")

    def connet_db(self):
        if self.dbname is None or self.tblname is None:
            logging.info("The db name or db table is None")
            return  None
        if self.con is None:
            ## Create/Connect to database
            self.con = sqlite3.connect(self.dbname)
        if self.cur is None:
            ## Create cursor, used to execute commands
            self.cur = self.con.cursor()
        return  self.con,self.cur

    def get_cursor(self):
        if self.dbname is None or self.tblname is None:
            logging.info("The db name or db table is None")
            return  None
        if self.cur is None:
            logging.info("The db Connect cursor is None")
            return None
        return self.cur

    def exec_select_all(self,select_sql,*args):
        logging.debug(f"exec_select sql: {select_sql} args:{args}")
        try:
            ## Check to see if text is already in database
            self.cur.execute(select_sql, args)
            result = self.cur.fetchall()

            ## If it is in DB, create log message
            if result:
                logging.debug(f"Item already in database: {args}")
                return result

            return None
        except Exception as e:
            logging.exception(f"exec_select database failed,{e}")

    def exec_select(self,select_sql,*args):
        logging.debug(f"exec_select sql: {select_sql} args:{args}")
        try:
            ## Check to see if text is already in database
            self.cur.execute(select_sql, args)
            result = self.cur.fetchone()

            ## If it is in DB, create log message
            if result:
                logging.debug(f"Item already in database: {args}")
                return result

            return None
        except Exception as e:
            logging.exception(f"exec_select database failed,{e}")

    def exec_insert(self, insert_sql, *args):
        logging.debug(f"exec_insert sql: {insert_sql} args:{args}")

        try:
            self.cur.execute(insert_sql,args)
            ## Execute insert of scrapyd into database
            self.con.commit()

        except Exception as e:
                logging.exception(f"exec_select database failed,{e}")

    def exec_update(self,update_sql, *args):
        logging.debug(f"exec_insert sql: {update_sql} args:{args}")

        try:
            ## Define update statement
            self.cur.execute(update_sql,args)
            ## Execute insert of scrapyd into database
            self.con.commit()
        except Exception as e:
                logging.exception(f"exec_select database failed,{e}")

    def close(self):
        if self.con:
            self.con.commit()
        if self.cur:
            self.cur.close()
            self.cur = None
        if self.con:
            self.con.close()
            self.con =None
